{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from Nobel\n",
    "\n",
    "## `nobel` Nobel Laureates\n",
    "\n",
    "We continue practicing simple SQL queries on a single table.\n",
    "\n",
    "This tutorial is concerned with a table of Nobel prize winners:\n",
    "\n",
    "```\n",
    "nobel(yr, subject, winner)\n",
    "```\n",
    "\n",
    "Using the `SELECT` statement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.3\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.0     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Winners from 1950\n",
    "\n",
    "Change the query shown so that it displays Nobel prizes for 1950."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "nobel <- dbReadTable(con, 'nobel')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 8 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1950</td><td>Chemistry </td><td>Kurt Alder       </td></tr>\n",
       "\t<tr><td>1950</td><td>Chemistry </td><td>Otto Diels       </td></tr>\n",
       "\t<tr><td>1950</td><td>Literature</td><td>Bertrand Russell </td></tr>\n",
       "\t<tr><td>1950</td><td>Medicine  </td><td>Philip S. Hench  </td></tr>\n",
       "\t<tr><td>1950</td><td>Medicine  </td><td>Edward C. Kendall</td></tr>\n",
       "\t<tr><td>1950</td><td>Medicine  </td><td>Tadeus Reichstein</td></tr>\n",
       "\t<tr><td>1950</td><td>Peace     </td><td>Ralph Bunche     </td></tr>\n",
       "\t<tr><td>1950</td><td>Physics   </td><td>Cecil Powell     </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 8 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1950 & Chemistry  & Kurt Alder       \\\\\n",
       "\t 1950 & Chemistry  & Otto Diels       \\\\\n",
       "\t 1950 & Literature & Bertrand Russell \\\\\n",
       "\t 1950 & Medicine   & Philip S. Hench  \\\\\n",
       "\t 1950 & Medicine   & Edward C. Kendall\\\\\n",
       "\t 1950 & Medicine   & Tadeus Reichstein\\\\\n",
       "\t 1950 & Peace      & Ralph Bunche     \\\\\n",
       "\t 1950 & Physics    & Cecil Powell     \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 8 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1950 | Chemistry  | Kurt Alder        |\n",
       "| 1950 | Chemistry  | Otto Diels        |\n",
       "| 1950 | Literature | Bertrand Russell  |\n",
       "| 1950 | Medicine   | Philip S. Hench   |\n",
       "| 1950 | Medicine   | Edward C. Kendall |\n",
       "| 1950 | Medicine   | Tadeus Reichstein |\n",
       "| 1950 | Peace      | Ralph Bunche      |\n",
       "| 1950 | Physics    | Cecil Powell      |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject    winner           \n",
       "1 1950 Chemistry  Kurt Alder       \n",
       "2 1950 Chemistry  Otto Diels       \n",
       "3 1950 Literature Bertrand Russell \n",
       "4 1950 Medicine   Philip S. Hench  \n",
       "5 1950 Medicine   Edward C. Kendall\n",
       "6 1950 Medicine   Tadeus Reichstein\n",
       "7 1950 Peace      Ralph Bunche     \n",
       "8 1950 Physics    Cecil Powell     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(yr==1950)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 1962 Literature\n",
    "\n",
    "Show who won the 1962 prize for Literature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>John Steinbeck</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " winner\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t John Steinbeck\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| winner &lt;chr&gt; |\n",
       "|---|\n",
       "| John Steinbeck |\n",
       "\n"
      ],
      "text/plain": [
       "  winner        \n",
       "1 John Steinbeck"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(yr==1962 & subject=='Literature') %>%\n",
    "    select(winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Albert Einstein\n",
    "\n",
    "Show the year and subject that won 'Albert Einstein' his prize."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1921</td><td>Physics</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 2\n",
       "\\begin{tabular}{ll}\n",
       " yr & subject\\\\\n",
       " <int> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1921 & Physics\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 2\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; |\n",
       "|---|---|\n",
       "| 1921 | Physics |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject\n",
       "1 1921 Physics"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(winner=='Albert Einstein') %>% \n",
    "    select(yr, subject)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Recent Peace Prizes\n",
    "\n",
    "Give the name of the 'Peace' winners since the year 2000, including 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 22 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Tunisian National Dialogue Quartet</td></tr>\n",
       "\t<tr><td>Kailash Satyarthi                 </td></tr>\n",
       "\t<tr><td>Malala Yousafzai                  </td></tr>\n",
       "\t<tr><td>European Union                    </td></tr>\n",
       "\t<tr><td>Ellen Johnson Sirleaf             </td></tr>\n",
       "\t<tr><td>Leymah Gbowee                     </td></tr>\n",
       "\t<tr><td>Tawakel Karman                    </td></tr>\n",
       "\t<tr><td>Liu Xiaobo                        </td></tr>\n",
       "\t<tr><td>Barack Obama                      </td></tr>\n",
       "\t<tr><td>Martti Ahtisaari                  </td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>Grameen Bank                      </td></tr>\n",
       "\t<tr><td>Muhammad Yunus                    </td></tr>\n",
       "\t<tr><td>International Atomic Energy Agency</td></tr>\n",
       "\t<tr><td>Mohamed ElBaradei                 </td></tr>\n",
       "\t<tr><td>Wangari Maathai                   </td></tr>\n",
       "\t<tr><td>Shirin Ebadi                      </td></tr>\n",
       "\t<tr><td>Jimmy Carter                      </td></tr>\n",
       "\t<tr><td>United Nations                    </td></tr>\n",
       "\t<tr><td>Kofi Annan                        </td></tr>\n",
       "\t<tr><td>Kim Dae-jung                      </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 22 × 1\n",
       "\\begin{tabular}{l}\n",
       " winner\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t Tunisian National Dialogue Quartet\\\\\n",
       "\t Kailash Satyarthi                 \\\\\n",
       "\t Malala Yousafzai                  \\\\\n",
       "\t European Union                    \\\\\n",
       "\t Ellen Johnson Sirleaf             \\\\\n",
       "\t Leymah Gbowee                     \\\\\n",
       "\t Tawakel Karman                    \\\\\n",
       "\t Liu Xiaobo                        \\\\\n",
       "\t Barack Obama                      \\\\\n",
       "\t Martti Ahtisaari                  \\\\\n",
       "\t ...\\\\\n",
       "\t Grameen Bank                      \\\\\n",
       "\t Muhammad Yunus                    \\\\\n",
       "\t International Atomic Energy Agency\\\\\n",
       "\t Mohamed ElBaradei                 \\\\\n",
       "\t Wangari Maathai                   \\\\\n",
       "\t Shirin Ebadi                      \\\\\n",
       "\t Jimmy Carter                      \\\\\n",
       "\t United Nations                    \\\\\n",
       "\t Kofi Annan                        \\\\\n",
       "\t Kim Dae-jung                      \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 22 × 1\n",
       "\n",
       "| winner &lt;chr&gt; |\n",
       "|---|\n",
       "| Tunisian National Dialogue Quartet |\n",
       "| Kailash Satyarthi                  |\n",
       "| Malala Yousafzai                   |\n",
       "| European Union                     |\n",
       "| Ellen Johnson Sirleaf              |\n",
       "| Leymah Gbowee                      |\n",
       "| Tawakel Karman                     |\n",
       "| Liu Xiaobo                         |\n",
       "| Barack Obama                       |\n",
       "| Martti Ahtisaari                   |\n",
       "| ... |\n",
       "| Grameen Bank                       |\n",
       "| Muhammad Yunus                     |\n",
       "| International Atomic Energy Agency |\n",
       "| Mohamed ElBaradei                  |\n",
       "| Wangari Maathai                    |\n",
       "| Shirin Ebadi                       |\n",
       "| Jimmy Carter                       |\n",
       "| United Nations                     |\n",
       "| Kofi Annan                         |\n",
       "| Kim Dae-jung                       |\n",
       "\n"
      ],
      "text/plain": [
       "    winner                            \n",
       "1   Tunisian National Dialogue Quartet\n",
       "2   Kailash Satyarthi                 \n",
       "3   Malala Yousafzai                  \n",
       "4   European Union                    \n",
       "5   Ellen Johnson Sirleaf             \n",
       "6   Leymah Gbowee                     \n",
       "7   Tawakel Karman                    \n",
       "8   Liu Xiaobo                        \n",
       "9   Barack Obama                      \n",
       "10  Martti Ahtisaari                  \n",
       "... ...                               \n",
       "13  Grameen Bank                      \n",
       "14  Muhammad Yunus                    \n",
       "15  International Atomic Energy Agency\n",
       "16  Mohamed ElBaradei                 \n",
       "17  Wangari Maathai                   \n",
       "18  Shirin Ebadi                      \n",
       "19  Jimmy Carter                      \n",
       "20  United Nations                    \n",
       "21  Kofi Annan                        \n",
       "22  Kim Dae-jung                      "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(yr>=2000 & subject=='Peace') %>% \n",
    "    select(winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Literature in the 1980's\n",
    "\n",
    "Show all details **(yr, subject, winner)** of the Literature prize winners for 1980 to 1989 inclusive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 10 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1989</td><td>Literature</td><td>Camilo José Cela      </td></tr>\n",
       "\t<tr><td>1988</td><td>Literature</td><td>Naguib Mahfouz        </td></tr>\n",
       "\t<tr><td>1987</td><td>Literature</td><td>Joseph Brodsky        </td></tr>\n",
       "\t<tr><td>1986</td><td>Literature</td><td>Wole Soyinka          </td></tr>\n",
       "\t<tr><td>1985</td><td>Literature</td><td>Claude Simon          </td></tr>\n",
       "\t<tr><td>1984</td><td>Literature</td><td>Jaroslav Seifert      </td></tr>\n",
       "\t<tr><td>1983</td><td>Literature</td><td>William Golding       </td></tr>\n",
       "\t<tr><td>1982</td><td>Literature</td><td>Gabriel García Márquez</td></tr>\n",
       "\t<tr><td>1981</td><td>Literature</td><td>Elias Canetti         </td></tr>\n",
       "\t<tr><td>1980</td><td>Literature</td><td>Czeslaw Milosz        </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 10 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1989 & Literature & Camilo José Cela      \\\\\n",
       "\t 1988 & Literature & Naguib Mahfouz        \\\\\n",
       "\t 1987 & Literature & Joseph Brodsky        \\\\\n",
       "\t 1986 & Literature & Wole Soyinka          \\\\\n",
       "\t 1985 & Literature & Claude Simon          \\\\\n",
       "\t 1984 & Literature & Jaroslav Seifert      \\\\\n",
       "\t 1983 & Literature & William Golding       \\\\\n",
       "\t 1982 & Literature & Gabriel García Márquez\\\\\n",
       "\t 1981 & Literature & Elias Canetti         \\\\\n",
       "\t 1980 & Literature & Czeslaw Milosz        \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 10 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1989 | Literature | Camilo José Cela       |\n",
       "| 1988 | Literature | Naguib Mahfouz         |\n",
       "| 1987 | Literature | Joseph Brodsky         |\n",
       "| 1986 | Literature | Wole Soyinka           |\n",
       "| 1985 | Literature | Claude Simon           |\n",
       "| 1984 | Literature | Jaroslav Seifert       |\n",
       "| 1983 | Literature | William Golding        |\n",
       "| 1982 | Literature | Gabriel García Márquez |\n",
       "| 1981 | Literature | Elias Canetti          |\n",
       "| 1980 | Literature | Czeslaw Milosz         |\n",
       "\n"
      ],
      "text/plain": [
       "   yr   subject    winner                \n",
       "1  1989 Literature Camilo José Cela      \n",
       "2  1988 Literature Naguib Mahfouz        \n",
       "3  1987 Literature Joseph Brodsky        \n",
       "4  1986 Literature Wole Soyinka          \n",
       "5  1985 Literature Claude Simon          \n",
       "6  1984 Literature Jaroslav Seifert      \n",
       "7  1983 Literature William Golding       \n",
       "8  1982 Literature Gabriel García Márquez\n",
       "9  1981 Literature Elias Canetti         \n",
       "10 1980 Literature Czeslaw Milosz        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(between(yr, 1980, 1989) & \n",
    "           subject=='Literature')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Only Presidents\n",
    "\n",
    "Show all details of the presidential winners:\n",
    "\n",
    "- Theodore Roosevelt\n",
    "- Woodrow Wilson\n",
    "- Jimmy Carter\n",
    "- Barack Obama"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 4 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2009</td><td>Peace</td><td>Barack Obama      </td></tr>\n",
       "\t<tr><td>2002</td><td>Peace</td><td>Jimmy Carter      </td></tr>\n",
       "\t<tr><td>1919</td><td>Peace</td><td>Woodrow Wilson    </td></tr>\n",
       "\t<tr><td>1906</td><td>Peace</td><td>Theodore Roosevelt</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 4 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 2009 & Peace & Barack Obama      \\\\\n",
       "\t 2002 & Peace & Jimmy Carter      \\\\\n",
       "\t 1919 & Peace & Woodrow Wilson    \\\\\n",
       "\t 1906 & Peace & Theodore Roosevelt\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 4 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 2009 | Peace | Barack Obama       |\n",
       "| 2002 | Peace | Jimmy Carter       |\n",
       "| 1919 | Peace | Woodrow Wilson     |\n",
       "| 1906 | Peace | Theodore Roosevelt |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject winner            \n",
       "1 2009 Peace   Barack Obama      \n",
       "2 2002 Peace   Jimmy Carter      \n",
       "3 1919 Peace   Woodrow Wilson    \n",
       "4 1906 Peace   Theodore Roosevelt"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(winner %in% c(\n",
    "        'Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. John\n",
    "\n",
    "Show the winners with first name John"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 26 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>John O'Keefe    </td></tr>\n",
       "\t<tr><td>John B. Gurdon  </td></tr>\n",
       "\t<tr><td>John C. Mather  </td></tr>\n",
       "\t<tr><td>John L. Hall    </td></tr>\n",
       "\t<tr><td>John B. Fenn    </td></tr>\n",
       "\t<tr><td>John E. Sulston </td></tr>\n",
       "\t<tr><td>John Pople      </td></tr>\n",
       "\t<tr><td>John Hume       </td></tr>\n",
       "\t<tr><td>John E. Walker  </td></tr>\n",
       "\t<tr><td>John C. Harsanyi</td></tr>\n",
       "\t<tr><td>...</td></tr>\n",
       "\t<tr><td>John Bardeen    </td></tr>\n",
       "\t<tr><td>John C. Kendrew </td></tr>\n",
       "\t<tr><td>John Steinbeck  </td></tr>\n",
       "\t<tr><td>John Bardeen    </td></tr>\n",
       "\t<tr><td>John F. Enders  </td></tr>\n",
       "\t<tr><td>John Cockcroft  </td></tr>\n",
       "\t<tr><td>John H. Northrop</td></tr>\n",
       "\t<tr><td>John R. Mott    </td></tr>\n",
       "\t<tr><td>John Galsworthy </td></tr>\n",
       "\t<tr><td>John Macleod    </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 26 × 1\n",
       "\\begin{tabular}{l}\n",
       " winner\\\\\n",
       " <chr>\\\\\n",
       "\\hline\n",
       "\t John O'Keefe    \\\\\n",
       "\t John B. Gurdon  \\\\\n",
       "\t John C. Mather  \\\\\n",
       "\t John L. Hall    \\\\\n",
       "\t John B. Fenn    \\\\\n",
       "\t John E. Sulston \\\\\n",
       "\t John Pople      \\\\\n",
       "\t John Hume       \\\\\n",
       "\t John E. Walker  \\\\\n",
       "\t John C. Harsanyi\\\\\n",
       "\t ...\\\\\n",
       "\t John Bardeen    \\\\\n",
       "\t John C. Kendrew \\\\\n",
       "\t John Steinbeck  \\\\\n",
       "\t John Bardeen    \\\\\n",
       "\t John F. Enders  \\\\\n",
       "\t John Cockcroft  \\\\\n",
       "\t John H. Northrop\\\\\n",
       "\t John R. Mott    \\\\\n",
       "\t John Galsworthy \\\\\n",
       "\t John Macleod    \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 26 × 1\n",
       "\n",
       "| winner &lt;chr&gt; |\n",
       "|---|\n",
       "| John O'Keefe     |\n",
       "| John B. Gurdon   |\n",
       "| John C. Mather   |\n",
       "| John L. Hall     |\n",
       "| John B. Fenn     |\n",
       "| John E. Sulston  |\n",
       "| John Pople       |\n",
       "| John Hume        |\n",
       "| John E. Walker   |\n",
       "| John C. Harsanyi |\n",
       "| ... |\n",
       "| John Bardeen     |\n",
       "| John C. Kendrew  |\n",
       "| John Steinbeck   |\n",
       "| John Bardeen     |\n",
       "| John F. Enders   |\n",
       "| John Cockcroft   |\n",
       "| John H. Northrop |\n",
       "| John R. Mott     |\n",
       "| John Galsworthy  |\n",
       "| John Macleod     |\n",
       "\n"
      ],
      "text/plain": [
       "    winner          \n",
       "1   John O'Keefe    \n",
       "2   John B. Gurdon  \n",
       "3   John C. Mather  \n",
       "4   John L. Hall    \n",
       "5   John B. Fenn    \n",
       "6   John E. Sulston \n",
       "7   John Pople      \n",
       "8   John Hume       \n",
       "9   John E. Walker  \n",
       "10  John C. Harsanyi\n",
       "... ...             \n",
       "17  John Bardeen    \n",
       "18  John C. Kendrew \n",
       "19  John Steinbeck  \n",
       "20  John Bardeen    \n",
       "21  John F. Enders  \n",
       "22  John Cockcroft  \n",
       "23  John H. Northrop\n",
       "24  John R. Mott    \n",
       "25  John Galsworthy \n",
       "26  John Macleod    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(str_starts(winner, 'John')) %>%\n",
    "    select(winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Chemistry and Physics from different years\n",
    "\n",
    "**Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1984</td><td>Chemistry</td><td>Bruce Merrifield</td></tr>\n",
       "\t<tr><td>1980</td><td>Physics  </td><td>James Cronin    </td></tr>\n",
       "\t<tr><td>1980</td><td>Physics  </td><td>Val Fitch       </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1984 & Chemistry & Bruce Merrifield\\\\\n",
       "\t 1980 & Physics   & James Cronin    \\\\\n",
       "\t 1980 & Physics   & Val Fitch       \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1984 | Chemistry | Bruce Merrifield |\n",
       "| 1980 | Physics   | James Cronin     |\n",
       "| 1980 | Physics   | Val Fitch        |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject   winner          \n",
       "1 1984 Chemistry Bruce Merrifield\n",
       "2 1980 Physics   James Cronin    \n",
       "3 1980 Physics   Val Fitch       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(yr==1980 & subject=='Physics' | \n",
    "           yr==1984 & subject=='Chemistry') %>%\n",
    "    select(yr, subject, winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Exclude Chemists and Medics\n",
    "\n",
    "**Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1980</td><td>Economics </td><td>Lawrence R. Klein    </td></tr>\n",
       "\t<tr><td>1980</td><td>Literature</td><td>Czeslaw Milosz       </td></tr>\n",
       "\t<tr><td>1980</td><td>Peace     </td><td>Adolfo Pérez Esquivel</td></tr>\n",
       "\t<tr><td>1980</td><td>Physics   </td><td>James Cronin         </td></tr>\n",
       "\t<tr><td>1980</td><td>Physics   </td><td>Val Fitch            </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1980 & Economics  & Lawrence R. Klein    \\\\\n",
       "\t 1980 & Literature & Czeslaw Milosz       \\\\\n",
       "\t 1980 & Peace      & Adolfo Pérez Esquivel\\\\\n",
       "\t 1980 & Physics    & James Cronin         \\\\\n",
       "\t 1980 & Physics    & Val Fitch            \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 5 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1980 | Economics  | Lawrence R. Klein     |\n",
       "| 1980 | Literature | Czeslaw Milosz        |\n",
       "| 1980 | Peace      | Adolfo Pérez Esquivel |\n",
       "| 1980 | Physics    | James Cronin          |\n",
       "| 1980 | Physics    | Val Fitch             |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject    winner               \n",
       "1 1980 Economics  Lawrence R. Klein    \n",
       "2 1980 Literature Czeslaw Milosz       \n",
       "3 1980 Peace      Adolfo Pérez Esquivel\n",
       "4 1980 Physics    James Cronin         \n",
       "5 1980 Physics    Val Fitch            "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(yr==1980 &\n",
    "           ! subject %in% c('Chemistry', \"Medicine\")) %>%\n",
    "    select(yr, subject, winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Early Medicine, Late Literature\n",
    "\n",
    "Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 23 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2015</td><td>Literature</td><td>Svetlana Alexievich         </td></tr>\n",
       "\t<tr><td>2014</td><td>Literature</td><td>Patrick Modiano             </td></tr>\n",
       "\t<tr><td>2013</td><td>Literature</td><td>Alice Munro                 </td></tr>\n",
       "\t<tr><td>2012</td><td>Literature</td><td>Mo Yan                      </td></tr>\n",
       "\t<tr><td>2011</td><td>Literature</td><td>Tomas Tranströmer           </td></tr>\n",
       "\t<tr><td>2010</td><td>Literature</td><td>Mario Vargas Llosa          </td></tr>\n",
       "\t<tr><td>2009</td><td>Literature</td><td>Herta Müller                </td></tr>\n",
       "\t<tr><td>2008</td><td>Literature</td><td>Jean-Marie Gustave Le Clézio</td></tr>\n",
       "\t<tr><td>2007</td><td>Literature</td><td>Doris Lessing               </td></tr>\n",
       "\t<tr><td>2006</td><td>Literature</td><td>Orhan Pamuk                 </td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>1908</td><td>Medicine</td><td>Paul Ehrlich          </td></tr>\n",
       "\t<tr><td>1908</td><td>Medicine</td><td>Ilya Mechnikov        </td></tr>\n",
       "\t<tr><td>1907</td><td>Medicine</td><td>Alphonse Laveran      </td></tr>\n",
       "\t<tr><td>1906</td><td>Medicine</td><td>Camillo Golgi         </td></tr>\n",
       "\t<tr><td>1906</td><td>Medicine</td><td>Santiago Ramón y Cajal</td></tr>\n",
       "\t<tr><td>1905</td><td>Medicine</td><td>Robert Koch           </td></tr>\n",
       "\t<tr><td>1904</td><td>Medicine</td><td>Ivan Pavlov           </td></tr>\n",
       "\t<tr><td>1903</td><td>Medicine</td><td>Niels Ryberg Finsen   </td></tr>\n",
       "\t<tr><td>1902</td><td>Medicine</td><td>Ronald Ross           </td></tr>\n",
       "\t<tr><td>1901</td><td>Medicine</td><td>Emil von Behring      </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 23 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 2015 & Literature & Svetlana Alexievich         \\\\\n",
       "\t 2014 & Literature & Patrick Modiano             \\\\\n",
       "\t 2013 & Literature & Alice Munro                 \\\\\n",
       "\t 2012 & Literature & Mo Yan                      \\\\\n",
       "\t 2011 & Literature & Tomas Tranströmer           \\\\\n",
       "\t 2010 & Literature & Mario Vargas Llosa          \\\\\n",
       "\t 2009 & Literature & Herta Müller                \\\\\n",
       "\t 2008 & Literature & Jean-Marie Gustave Le Clézio\\\\\n",
       "\t 2007 & Literature & Doris Lessing               \\\\\n",
       "\t 2006 & Literature & Orhan Pamuk                 \\\\\n",
       "\t ... & ... & ...\\\\\n",
       "\t 1908 & Medicine & Paul Ehrlich          \\\\\n",
       "\t 1908 & Medicine & Ilya Mechnikov        \\\\\n",
       "\t 1907 & Medicine & Alphonse Laveran      \\\\\n",
       "\t 1906 & Medicine & Camillo Golgi         \\\\\n",
       "\t 1906 & Medicine & Santiago Ramón y Cajal\\\\\n",
       "\t 1905 & Medicine & Robert Koch           \\\\\n",
       "\t 1904 & Medicine & Ivan Pavlov           \\\\\n",
       "\t 1903 & Medicine & Niels Ryberg Finsen   \\\\\n",
       "\t 1902 & Medicine & Ronald Ross           \\\\\n",
       "\t 1901 & Medicine & Emil von Behring      \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 23 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 2015 | Literature | Svetlana Alexievich          |\n",
       "| 2014 | Literature | Patrick Modiano              |\n",
       "| 2013 | Literature | Alice Munro                  |\n",
       "| 2012 | Literature | Mo Yan                       |\n",
       "| 2011 | Literature | Tomas Tranströmer            |\n",
       "| 2010 | Literature | Mario Vargas Llosa           |\n",
       "| 2009 | Literature | Herta Müller                 |\n",
       "| 2008 | Literature | Jean-Marie Gustave Le Clézio |\n",
       "| 2007 | Literature | Doris Lessing                |\n",
       "| 2006 | Literature | Orhan Pamuk                  |\n",
       "| ... | ... | ... |\n",
       "| 1908 | Medicine | Paul Ehrlich           |\n",
       "| 1908 | Medicine | Ilya Mechnikov         |\n",
       "| 1907 | Medicine | Alphonse Laveran       |\n",
       "| 1906 | Medicine | Camillo Golgi          |\n",
       "| 1906 | Medicine | Santiago Ramón y Cajal |\n",
       "| 1905 | Medicine | Robert Koch            |\n",
       "| 1904 | Medicine | Ivan Pavlov            |\n",
       "| 1903 | Medicine | Niels Ryberg Finsen    |\n",
       "| 1902 | Medicine | Ronald Ross            |\n",
       "| 1901 | Medicine | Emil von Behring       |\n",
       "\n"
      ],
      "text/plain": [
       "    yr   subject    winner                      \n",
       "1   2015 Literature Svetlana Alexievich         \n",
       "2   2014 Literature Patrick Modiano             \n",
       "3   2013 Literature Alice Munro                 \n",
       "4   2012 Literature Mo Yan                      \n",
       "5   2011 Literature Tomas Tranströmer           \n",
       "6   2010 Literature Mario Vargas Llosa          \n",
       "7   2009 Literature Herta Müller                \n",
       "8   2008 Literature Jean-Marie Gustave Le Clézio\n",
       "9   2007 Literature Doris Lessing               \n",
       "10  2006 Literature Orhan Pamuk                 \n",
       "... ...  ...        ...                         \n",
       "14  1908 Medicine   Paul Ehrlich                \n",
       "15  1908 Medicine   Ilya Mechnikov              \n",
       "16  1907 Medicine   Alphonse Laveran            \n",
       "17  1906 Medicine   Camillo Golgi               \n",
       "18  1906 Medicine   Santiago Ramón y Cajal      \n",
       "19  1905 Medicine   Robert Koch                 \n",
       "20  1904 Medicine   Ivan Pavlov                 \n",
       "21  1903 Medicine   Niels Ryberg Finsen         \n",
       "22  1902 Medicine   Ronald Ross                 \n",
       "23  1901 Medicine   Emil von Behring            "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter((subject=='Medicine' & yr<1910) | \n",
    "           (subject=='Literature' & yr>=2004)) %>%\n",
    "    select(yr, subject, winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Umlaut\n",
    "\n",
    "Find all details of the prize won by PETER GRÜNBERG\n",
    "\n",
    "> _Non-ASCII characters_   \n",
    "> The u in his name has an umlaut. You may find this link useful <https://en.wikipedia.org/wiki/%C3%9C#Keyboarding>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2007</td><td>Physics</td><td>Peter Grünberg</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 2007 & Physics & Peter Grünberg\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 2007 | Physics | Peter Grünberg |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject winner        \n",
       "1 2007 Physics Peter Grünberg"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(tolower(winner)=='peter grünberg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Apostrophe\n",
    "\n",
    "Find all details of the prize won by EUGENE O'NEILL\n",
    "\n",
    "> _Escaping single quotes_   \n",
    "> You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>yr</th><th scope=col>subject</th><th scope=col>winner</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1936</td><td>Literature</td><td>Eugene O'Neill</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 3\n",
       "\\begin{tabular}{lll}\n",
       " yr & subject & winner\\\\\n",
       " <int> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 1936 & Literature & Eugene O'Neill\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 3\n",
       "\n",
       "| yr &lt;int&gt; | subject &lt;chr&gt; | winner &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 1936 | Literature | Eugene O'Neill |\n",
       "\n"
      ],
      "text/plain": [
       "  yr   subject    winner        \n",
       "1 1936 Literature Eugene O'Neill"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(tolower(winner)==\"eugene o'neill\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Knights of the realm\n",
    "\n",
    "Knights in order\n",
    "\n",
    "**List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 21 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>winner</th><th scope=col>yr</th><th scope=col>subject</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Sir Martin J. Evans        </td><td>2007</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Peter Mansfield        </td><td>2003</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Paul Nurse             </td><td>2001</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Harold Kroto           </td><td>1996</td><td>Chemistry</td></tr>\n",
       "\t<tr><td>Sir James W. Black         </td><td>1988</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Arthur Lewis           </td><td>1979</td><td>Economics</td></tr>\n",
       "\t<tr><td>Sir Nevill F. Mott         </td><td>1977</td><td>Physics  </td></tr>\n",
       "\t<tr><td>Sir Bernard Katz           </td><td>1970</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir John Eccles            </td><td>1963</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Frank Macfarlane Burnet</td><td>1960</td><td>Medicine </td></tr>\n",
       "\t<tr><td>...</td><td>...</td><td>...</td></tr>\n",
       "\t<tr><td>Sir Robert Robinson    </td><td>1947</td><td>Chemistry</td></tr>\n",
       "\t<tr><td>Sir Alexander Fleming  </td><td>1945</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Howard Florey      </td><td>1945</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Henry Dale         </td><td>1936</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Norman Angell      </td><td>1933</td><td>Peace    </td></tr>\n",
       "\t<tr><td>Sir Charles Sherrington</td><td>1932</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Venkata Raman      </td><td>1930</td><td>Physics  </td></tr>\n",
       "\t<tr><td>Sir Frederick Hopkins  </td><td>1929</td><td>Medicine </td></tr>\n",
       "\t<tr><td>Sir Austen Chamberlain </td><td>1925</td><td>Peace    </td></tr>\n",
       "\t<tr><td>Sir William Ramsay     </td><td>1904</td><td>Chemistry</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 21 × 3\n",
       "\\begin{tabular}{lll}\n",
       " winner & yr & subject\\\\\n",
       " <chr> & <int> & <chr>\\\\\n",
       "\\hline\n",
       "\t Sir Martin J. Evans         & 2007 & Medicine \\\\\n",
       "\t Sir Peter Mansfield         & 2003 & Medicine \\\\\n",
       "\t Sir Paul Nurse              & 2001 & Medicine \\\\\n",
       "\t Sir Harold Kroto            & 1996 & Chemistry\\\\\n",
       "\t Sir James W. Black          & 1988 & Medicine \\\\\n",
       "\t Sir Arthur Lewis            & 1979 & Economics\\\\\n",
       "\t Sir Nevill F. Mott          & 1977 & Physics  \\\\\n",
       "\t Sir Bernard Katz            & 1970 & Medicine \\\\\n",
       "\t Sir John Eccles             & 1963 & Medicine \\\\\n",
       "\t Sir Frank Macfarlane Burnet & 1960 & Medicine \\\\\n",
       "\t ... & ... & ...\\\\\n",
       "\t Sir Robert Robinson     & 1947 & Chemistry\\\\\n",
       "\t Sir Alexander Fleming   & 1945 & Medicine \\\\\n",
       "\t Sir Howard Florey       & 1945 & Medicine \\\\\n",
       "\t Sir Henry Dale          & 1936 & Medicine \\\\\n",
       "\t Sir Norman Angell       & 1933 & Peace    \\\\\n",
       "\t Sir Charles Sherrington & 1932 & Medicine \\\\\n",
       "\t Sir Venkata Raman       & 1930 & Physics  \\\\\n",
       "\t Sir Frederick Hopkins   & 1929 & Medicine \\\\\n",
       "\t Sir Austen Chamberlain  & 1925 & Peace    \\\\\n",
       "\t Sir William Ramsay      & 1904 & Chemistry\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 21 × 3\n",
       "\n",
       "| winner &lt;chr&gt; | yr &lt;int&gt; | subject &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| Sir Martin J. Evans         | 2007 | Medicine  |\n",
       "| Sir Peter Mansfield         | 2003 | Medicine  |\n",
       "| Sir Paul Nurse              | 2001 | Medicine  |\n",
       "| Sir Harold Kroto            | 1996 | Chemistry |\n",
       "| Sir James W. Black          | 1988 | Medicine  |\n",
       "| Sir Arthur Lewis            | 1979 | Economics |\n",
       "| Sir Nevill F. Mott          | 1977 | Physics   |\n",
       "| Sir Bernard Katz            | 1970 | Medicine  |\n",
       "| Sir John Eccles             | 1963 | Medicine  |\n",
       "| Sir Frank Macfarlane Burnet | 1960 | Medicine  |\n",
       "| ... | ... | ... |\n",
       "| Sir Robert Robinson     | 1947 | Chemistry |\n",
       "| Sir Alexander Fleming   | 1945 | Medicine  |\n",
       "| Sir Howard Florey       | 1945 | Medicine  |\n",
       "| Sir Henry Dale          | 1936 | Medicine  |\n",
       "| Sir Norman Angell       | 1933 | Peace     |\n",
       "| Sir Charles Sherrington | 1932 | Medicine  |\n",
       "| Sir Venkata Raman       | 1930 | Physics   |\n",
       "| Sir Frederick Hopkins   | 1929 | Medicine  |\n",
       "| Sir Austen Chamberlain  | 1925 | Peace     |\n",
       "| Sir William Ramsay      | 1904 | Chemistry |\n",
       "\n"
      ],
      "text/plain": [
       "    winner                      yr   subject  \n",
       "1   Sir Martin J. Evans         2007 Medicine \n",
       "2   Sir Peter Mansfield         2003 Medicine \n",
       "3   Sir Paul Nurse              2001 Medicine \n",
       "4   Sir Harold Kroto            1996 Chemistry\n",
       "5   Sir James W. Black          1988 Medicine \n",
       "6   Sir Arthur Lewis            1979 Economics\n",
       "7   Sir Nevill F. Mott          1977 Physics  \n",
       "8   Sir Bernard Katz            1970 Medicine \n",
       "9   Sir John Eccles             1963 Medicine \n",
       "10  Sir Frank Macfarlane Burnet 1960 Medicine \n",
       "... ...                         ...  ...      \n",
       "12  Sir Robert Robinson         1947 Chemistry\n",
       "13  Sir Alexander Fleming       1945 Medicine \n",
       "14  Sir Howard Florey           1945 Medicine \n",
       "15  Sir Henry Dale              1936 Medicine \n",
       "16  Sir Norman Angell           1933 Peace    \n",
       "17  Sir Charles Sherrington     1932 Medicine \n",
       "18  Sir Venkata Raman           1930 Physics  \n",
       "19  Sir Frederick Hopkins       1929 Medicine \n",
       "20  Sir Austen Chamberlain      1925 Peace    \n",
       "21  Sir William Ramsay          1904 Chemistry"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>% \n",
    "    filter(str_starts(tolower(winner), 'sir.')) %>% \n",
    "    select(winner, yr, subject) %>% \n",
    "    arrange(-yr, winner)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. Chemistry and Physics last\n",
    "\n",
    "The expression **subject IN ('Chemistry','Physics')** can be used as a value - it will be 0 or 1.\n",
    "\n",
    "**Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 9 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>winner</th><th scope=col>subject</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Richard Stone      </td><td>Economics </td></tr>\n",
       "\t<tr><td>Jaroslav Seifert   </td><td>Literature</td></tr>\n",
       "\t<tr><td>César Milstein     </td><td>Medicine  </td></tr>\n",
       "\t<tr><td>Georges J.F. Köhler</td><td>Medicine  </td></tr>\n",
       "\t<tr><td>Niels K. Jerne     </td><td>Medicine  </td></tr>\n",
       "\t<tr><td>Desmond Tutu       </td><td>Peace     </td></tr>\n",
       "\t<tr><td>Bruce Merrifield   </td><td>Chemistry </td></tr>\n",
       "\t<tr><td>Carlo Rubbia       </td><td>Physics   </td></tr>\n",
       "\t<tr><td>Simon van der Meer </td><td>Physics   </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 9 × 2\n",
       "\\begin{tabular}{ll}\n",
       " winner & subject\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Richard Stone       & Economics \\\\\n",
       "\t Jaroslav Seifert    & Literature\\\\\n",
       "\t César Milstein      & Medicine  \\\\\n",
       "\t Georges J.F. Köhler & Medicine  \\\\\n",
       "\t Niels K. Jerne      & Medicine  \\\\\n",
       "\t Desmond Tutu        & Peace     \\\\\n",
       "\t Bruce Merrifield    & Chemistry \\\\\n",
       "\t Carlo Rubbia        & Physics   \\\\\n",
       "\t Simon van der Meer  & Physics   \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 9 × 2\n",
       "\n",
       "| winner &lt;chr&gt; | subject &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Richard Stone       | Economics  |\n",
       "| Jaroslav Seifert    | Literature |\n",
       "| César Milstein      | Medicine   |\n",
       "| Georges J.F. Köhler | Medicine   |\n",
       "| Niels K. Jerne      | Medicine   |\n",
       "| Desmond Tutu        | Peace      |\n",
       "| Bruce Merrifield    | Chemistry  |\n",
       "| Carlo Rubbia        | Physics    |\n",
       "| Simon van der Meer  | Physics    |\n",
       "\n"
      ],
      "text/plain": [
       "  winner              subject   \n",
       "1 Richard Stone       Economics \n",
       "2 Jaroslav Seifert    Literature\n",
       "3 César Milstein      Medicine  \n",
       "4 Georges J.F. Köhler Medicine  \n",
       "5 Niels K. Jerne      Medicine  \n",
       "6 Desmond Tutu        Peace     \n",
       "7 Bruce Merrifield    Chemistry \n",
       "8 Carlo Rubbia        Physics   \n",
       "9 Simon van der Meer  Physics   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nobel %>%\n",
    "    filter(yr==1984) %>%\n",
    "    arrange(subject %in% c('Chemistry', 'Physics'), subject, winner) %>% \n",
    "    select(winner, subject)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
